/****** Object:  UserDefinedFunction [dbo].[fn_EncryptList]    Script Date: 06/09/2015 17:05:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_EncryptList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_EncryptList]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_EncryptList]    Script Date: 06/09/2015 17:05:24 ******/
CREATE FUNCTION [dbo].[fn_EncryptList] 
(
	@plainTextList CryptoUpdater readonly
)
RETURNS @cipherTextList TABLE (PlainText VARCHAR(20), CipherText VARCHAR(50))
AS
BEGIN

DECLARE @tmp TABLE (PlainText VARCHAR(20), CipherBinary VARBINARY(255), CipherText VARCHAR(50))

INSERT @tmp(PlainText) 
SELECT PlainText 
FROM @plainTextList

UPDATE @tmp 
SET CipherBinary = EncryptByPassPhrase([Global].dbo.fn_OpenSesame(), PlainText)

UPDATE @tmp 
SET CipherText = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("CipherBinary")))', 'varchar(50)')

INSERT @cipherTextList 
SELECT PlainText, CipherText 
FROM @tmp

RETURN

END
GO